package com.tnl.app.dao;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.tnl.app.entity.Employer;
import com.tnl.base.BaseDAO;


@Repository
public class EmployerDAO {

	static Logger logger = LoggerFactory.getLogger(EmployerDAO.class);

	protected static final String SQL_SAVE = "insert into tb_employer(openid,name,"
			+ "phone,telephone,email,binded,"
			+ "active,createDate,companyId, companyname) "
			+ "values(?,?,?,?" + ",?,?,?,?" + ",?,?)";

	protected static final String SQL_UPDATE = "update tb_employer set openid=?, name=?, englishname = ?"
			+ ",phone=?,telephone =?,email=?,binded=?, "
			+ " active=?, createDate=?, companyId=?, companyname=?, imgUrl=? ,state=?, headImgUrl=?,roleName=? where id =?";

	private static final String SQL_DELETE = "delete from tb_employer where id=?";

	private static final String SQL_GET_ID = "select * from tb_employer where id = ? ";

	private static final String SQL_GET_OPENID_STATUS = "select * from tb_employer where openid = ? and active =? ";

	private static final String SQL_GET_ALL = "select * from tb_employer";

	private static final String SQL_GET_COMPANY = "select * from  tb_employer "
			+ " where binded = 1 and companyId = ?";
	
	private static final String SQL_GET_COMPANY_NO = "select count(*) from  tb_employer "
			+ " where binded = 1 and companyId = ? ";

	protected static final String SQL_GET_BY_PHONE = "select * from tb_employer  where phone = ?";
	protected static final String SQL_GET_BY_MAIL = "select * from tb_employer  where email = ?";

	private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_employer where 1=1 ";
	private static final String SQL_GET_ALL_WITH_LIMIT = "select * from tb_employer where 1=1 ";

	public int save(Employer employer) {
		if (employer == null) {
			return -1;
		}
		Long id = BaseDAO.save(SQL_SAVE, employer.getOpenid(), employer.getName(),
				 employer.getPhone(),employer.getTelephone(), employer.getEmail(),
				 employer.getBinded(),employer.getActive(),
				employer.getCreateDate(), employer.getCompanyId(),
				employer.getCompanyName());
		if (id == null) {
			return -1;
		}

		return id.intValue();
	}

	public int delete(String Id) {

		if (Id != null) {

			int result = BaseDAO.execute(SQL_DELETE, Id);
			return result;
		} else {
			return 0;
		}
	}

	public List<Employer> getALL() {
		return BaseDAO.queryForList(Employer.class, SQL_GET_ALL);
	}

	public int update(Employer employer) {
		return BaseDAO.execute(SQL_UPDATE, employer.getOpenid(),
				employer.getName(), employer.getEnglishname(),
				employer.getPhone(), employer.getTelephone(), employer.getEmail(),
				employer.getBinded(),  employer.getActive(),
				employer.getCreateDate(), employer.getCompanyId(),
				employer.getCompanyName(), employer.getImgUrl(),employer.getState(),
				employer.getHeadImgUrl(), employer.getRoleName(), employer.getId());
	}

	public Employer getById(Integer id) {
		return BaseDAO.queryForObject(Employer.class, SQL_GET_ID, id);
	}

	public Employer getByOpenId(String openid) {
		return this.getByOpenId(openid,Employer.activate);
	}
	
	public Employer getByOpenId(String openid, int status) {
		return BaseDAO.queryForObject(Employer.class, SQL_GET_OPENID_STATUS, openid, status);
	}

	public List<Employer> getBindedByCompanyId(int companyId) {
		return BaseDAO.queryForList(Employer.class, SQL_GET_COMPANY, companyId);
	}

	public Long getBindedNumByCompanyId(int companyId, String tempcompanyName) {
		if (StringUtils.hasText(tempcompanyName))
		{
			String sql = SQL_GET_COMPANY_NO + " and companyName like ?";
			return (Long)BaseDAO.queryOneRowOneCol(sql, companyId ,tempcompanyName);
		}
		return (Long)BaseDAO.queryOneRowOneCol(SQL_GET_COMPANY_NO, companyId);
	}
	public Employer getByPhone(String phone) {
		return BaseDAO.queryForObject(Employer.class, SQL_GET_BY_PHONE, phone);
	}
	
	
	public Employer getByEmail(String mailAddr) {
    return BaseDAO.queryForObject(Employer.class, SQL_GET_BY_MAIL, mailAddr);
  }

	public Long getCountOfAll(String name, Integer companyId, String phone) {
		String sql = SQL_GET_ALL_COUNT;
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "'";
		}
		if (companyId != null) {
			if (companyId != 0)
			{
				sql += " and companyId =" + companyId + " ";
			}
			else
			{
				sql += " and companyname is not null ";
			}
		}
		if (StringUtils.hasText(phone)) {
			sql += " and phone like '" + phone + "' ";
		}
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}

	public List<Employer> getAllWithLimit(String name, Integer companyId,
			String phone, long start, long length) {
		String sql = SQL_GET_ALL_WITH_LIMIT;
		if (StringUtils.hasText(name)) {
			sql += " and name like '" + name + "'";
		}
		if (companyId != null) {
			if (companyId != 0)
			{
				sql += " and companyId =" + companyId + " ";
			}
			else
			{
				sql += " and companyname is not null ";
			}
			
		}
		if (StringUtils.hasText(phone)) {
			sql += " and phone like '" + phone + "' ";
		}
		sql += " LIMIT ?, ?";
		return BaseDAO.queryForList(Employer.class, sql, start, length);
	}
	
	protected static final String SQL_SAVE_SECURECODE = "insert into tb_employer(openid," + "email, secureCode,expiredTime,state,active ) " + "values(?,?,?,?,?,?)";

	public int saveSecureCode(Employer hr) {
    if (hr == null) {
      return -1;
    }
    Long id = BaseDAO.save(SQL_SAVE_SECURECODE, hr.getOpenid(), hr.getEmail(), 
                           hr.getSecureCode(), hr.getExpiredTime(), hr.getState(),
                           hr.getActive());
    if (id == null) {
      return -1;
    }

    return id.intValue();
  }


  private static final String SQL_GET_OPENID_EMAIL = "select * from tb_employer where OPENID   = ? and email =? ";

  public Employer findByOpenIdandEmail(String openId, String email) {
    return BaseDAO.queryForObject(Employer.class, SQL_GET_OPENID_EMAIL, openId, email);
  }
  protected static final String SQL_UPDATE_EMAIL = "update tb_employer set openid=?, email=?,state=? where id =?";

  public long updateBindedEmail(Employer hr) {
    return BaseDAO.execute(SQL_UPDATE_EMAIL, hr.getOpenid(), hr.getEmail(), hr.getState(), hr.getId());
    
  }
  protected static final String SQL_UPDATE_COMP = "update tb_employer set state=?, headImgUrl= ? where id =?";

  public long updateCompanyInfo(Employer hr) {
    return BaseDAO.execute(SQL_UPDATE_COMP,  hr.getState(),hr.getHeadImgUrl(), hr.getId());
    
  }
}
